﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using cwtrock.model;
using System.Data;
using cwtrock.util;

namespace cwtrock.dao.impl
{
    class SysCurrencyDaoImpl : BasicDaoImpl
    {
        private GetConn getConn = new GetConn();

        public int save(SysCurrency currency)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("INSERT INTO [SYS_CURRENCY]");
                sql.Append("([C_CODE],[C_NAME] ,[C_RATE])");
                sql.Append(" VALUES(@code,@name,@rate)");

                SqlParameter[] parameters ={
                new SqlParameter("@code",SqlDbType.VarChar,50),   
                new SqlParameter("@name",SqlDbType.VarChar,50), 
                new SqlParameter("@rate",SqlDbType.VarChar,50)
                };
                parameters[0].Value = currency.CURRENCY_CODE;
                parameters[1].Value = currency.CURRENCY_NAME;
                parameters[2].Value = currency.CURRENCY_RATE;

                SqlCommand cmd = new SqlCommand(sql.ToString(), getConn.GetCon());
                cmd.Parameters.Add(parameters[0]);
                cmd.Parameters.Add(parameters[1]);
                cmd.Parameters.Add(parameters[2]);
                int rows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return rows;

            }
            catch (Exception e)
            {
                throw e;
                return 0;
            }
           
        }

        public int update(SysCurrency currency)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("UPDATE [SYS_CURRENCY]");
                sql.Append(" SET [C_CODE] = @code");
                sql.Append(" ,[C_NAME]  = @name");
                sql.Append(" ,[C_RATE] =@rate");
                sql.Append(" WHERE id = @id");

                SqlParameter[] parameters ={
                new SqlParameter("@id",SqlDbType.Int),   
                new SqlParameter("@code",SqlDbType.VarChar,50),   
                new SqlParameter("@name",SqlDbType.VarChar,50), 
                new SqlParameter("@rate",SqlDbType.VarChar,50)
                };
                parameters[0].Value = currency.CURRENCY_ID;
                parameters[1].Value = currency.CURRENCY_CODE;
                parameters[2].Value = currency.CURRENCY_NAME;
                parameters[3].Value = currency.CURRENCY_RATE;

                SqlCommand cmd = new SqlCommand(sql.ToString(), getConn.GetCon());
                cmd.Parameters.Add(parameters[0]);
                cmd.Parameters.Add(parameters[1]);
                cmd.Parameters.Add(parameters[2]);
                cmd.Parameters.Add(parameters[3]);

                int rows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return rows;

            }
            catch (Exception e)
            {
                throw e;
                return 0;
            }

        }

        public List<SysCurrency> getAllCurrency()
        {
            String sql = "SELECT [ID] ,[C_CODE],[C_NAME] ,[C_RATE] FROM [SYS_CURRENCY]";
            SqlCommand cmd = new SqlCommand(sql.ToString(), getConn.GetCon());
            SqlDataReader reader = cmd.ExecuteReader();
            List<SysCurrency> res = new List<SysCurrency>();

            while (reader.Read())
            {
                SysCurrency cu = new SysCurrency();
                cu.CURRENCY_ID = int.Parse(reader[0].ToString());
                cu.CURRENCY_CODE = reader[1].ToString();
                cu.CURRENCY_NAME = reader[2].ToString();
                cu.CURRENCY_RATE = reader[3].ToString();
                res.Add(cu);
            }
            return res;
        }

        public SysCurrency getSysCurrency(int id)
        {
            String sql = "SELECT top 1 [ID] ,[C_CODE],[C_NAME] ,[C_RATE] FROM [SYS_CURRENCY] where id = @id";
            SqlParameter[] parameters = { 
                new SqlParameter("@id",SqlDbType.Int)                             
            };
            parameters[0].Value = id ;

            SqlCommand cmd = new SqlCommand(sql.ToString(), getConn.GetCon());
            cmd.Parameters.Add(parameters[0]) ;

            SqlDataReader reader = cmd.ExecuteReader();
            SysCurrency res = null ;

            while (reader.Read())
            {
                res = new SysCurrency();
                res.CURRENCY_ID = int.Parse(reader[0].ToString());
                res.CURRENCY_CODE = reader[1].ToString();
                res.CURRENCY_NAME = reader[2].ToString();
                res.CURRENCY_RATE = reader[3].ToString();
            }
            return res;
        }

        public int delSysCurrency(int id)
        {
            StringBuilder sql = new StringBuilder("DELETE FROM [cwtrock].[dbo].[SYS_CURRENCY] WHERE ");
            sql.Append(" id = @id ");

            SqlParameter[] parameters = { 
                new SqlParameter("@id",SqlDbType.Int)                             
            };
            parameters[0].Value = id;

            SqlCommand cmd = new SqlCommand(sql.ToString(), getConn.GetCon());
            cmd.Parameters.Add(parameters[0]);
            int rows = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return rows;

        }

    }
}
